package com.jsyl.dao.authority;

import com.alibaba.fastjson.JSONArray;
import com.alibaba.fastjson.JSONObject;
import com.jsyl.dao.base.BaseDao;
import com.jsyl.dao.base.SqlBuilder;
import com.jsyl.entity.authority.Child;
import com.jsyl.entity.authority.ClassSchedule;
import com.jsyl.entity.authority.Score;
import com.jsyl.infra.utils.page.PageModel;
import org.apache.commons.lang3.ObjectUtils;
import org.apache.commons.lang3.StringUtils;
import org.springframework.stereotype.Repository;

import java.text.SimpleDateFormat;
import java.util.*;


/**
 *
 *
 */
@Repository
public class ClassScheduleDao extends BaseDao<ClassSchedule> {

    //找中心
    public List findCenter() {
        StringBuilder sql = new StringBuilder();
        StringBuilder whereSql = new StringBuilder();
        sql.append(SqlBuilder.SQL_SELECT).append("center_name").append(SqlBuilder.SQL_FROM);
        whereSql.append("class_schedule_detail").append(" group by center_name");

        Map<String, Object> paramMap = new HashMap<String, Object>();
        sql.append(whereSql);
        logger.info("SQL : " + "\"" + sql.toString() + "\"");
        List<Map<String, Object>> resultList = namedTemplate.queryForList(sql.toString(), paramMap);
        List centerName = new ArrayList();
        for (int i = 0; i < resultList.size(); i++) {
            centerName.add(resultList.get(i).get("center_name"));
        }

        return centerName;
    }

    @Override
    public PageModel<ClassSchedule> query(PageModel<ClassSchedule> pm, ClassSchedule classSchedule) {
        StringBuilder sql = new StringBuilder();
        StringBuilder totalsql = new StringBuilder();
        StringBuilder whereSql = new StringBuilder();
        sql.append(SqlBuilder.SQL_SELECT).append("sd.child_id AS CHILDID,sd.schedule_detail_id AS scheduleId,sp.projects AS PROJECTS,sd.schedule_detail_id AS ID,cs.course_id AS COURSEID,cs.course_name AS COURSENAME,cs.start_time AS STARTIME,cs.end_time AS ENDTIME,cs.week AS WEEK").append(SqlBuilder.SQL_FROM);
        totalsql.append(SqlBuilder.SQL_SELECT).append("COUNT(1)").append(SqlBuilder.SQL_FROM);
        whereSql.append(
                "schedule_detail_child sd\n" +
                        "LEFT JOIN schedule_child_projects sp ON sd.schedule_detail_id = sp.schedule_id and sd.child_id = sp.child_id\n" +
                        "LEFT JOIN class_schedule_detail cs ON sd.schedule_detail_id = cs.id ")
                .append(SqlBuilder.SQL_WHERE).append("1=1 ");

        Map<String, Object> paramMap = new HashMap<String, Object>();
        if (StringUtils.isNotEmpty(classSchedule.getChildId())) {
            whereSql.append(SqlBuilder.SQL_AND).append("sd.child_id").append(" = ").append(":child_id");
            paramMap.put("child_id", classSchedule.getChildId());
        }
//        if (StringUtils.isNotEmpty(classSchedule.getTeacherId())) {
//            whereSql.append(SqlBuilder.SQL_AND).append("cs.teacher_id").append(" = ").append(":teacher_id");
//            paramMap.put("teacher_id", classSchedule.getTeacherId());
//        }
        if (StringUtils.isNotEmpty(classSchedule.getCourseId())) {
            whereSql.append(SqlBuilder.SQL_AND).append("cs.course_id").append(" = ").append(":course_id");
            paramMap.put("course_id", classSchedule.getCourseId());
        }

        if (null != classSchedule.getId()) {
            whereSql.append(SqlBuilder.SQL_AND).append("sd.schedule_detail_id = ").append(":scheduleDetailId");
            paramMap.put("scheduleDetailId", classSchedule.getId());
        }

        totalsql.append(whereSql);
        whereSql.append(" order by STARTIME");
        logger.info("totalsql : " + "\"" + totalsql.toString() + "\"");
        Integer totalCount = namedTemplate.queryForObject(totalsql.toString(), paramMap, Long.class).intValue();


        Integer startIndex = pm.getStartIndex();
        Integer pageSize = pm.getPageSize();

        if (pageSize == null || pageSize <= 0) {
            pageSize = PageModel.DEFAULT_PAGE_SIZE;
        }

        if (startIndex == null || startIndex < 0) {
            startIndex = 0;
        } else if (startIndex > totalCount) {
            Integer totalPage = (int) Math.ceil(totalCount.doubleValue() / pageSize.doubleValue());
            startIndex = (totalPage - 1) * pageSize;
        }

        whereSql.append(SqlBuilder.SQL_LIMIT);
        if (startIndex != null && startIndex >= 0) {
            whereSql.append(startIndex).append(",");
        }
        whereSql.append(pageSize);

        sql.append(whereSql);

        logger.info("SQL : " + "\"" + sql.toString() + "\"");
        List<Map<String, Object>> resultList = namedTemplate.queryForList(sql.toString(), paramMap);
        Map<String, String> columnMapper = new HashMap<>();
        List<ClassSchedule> list = new ArrayList<ClassSchedule>();
        ClassSchedule vo = null;
        for (Iterator<Map<String, Object>> iterator = resultList.iterator(); iterator.hasNext(); ) {
            Map<String, Object> record = iterator.next();
            vo = new ClassSchedule();
            vo.setId((Integer) record.get("ID"));
            String courseId = "";
            String courseName = "";
            String startTime = "";
            String endTime = "";
            String status = "暂时没有签到信息";
            String week = "";
            String date = "";
            String time = "";

            if (null != ((Integer) record.get("WEEK"))) {
                Integer weekval = (Integer) record.get("WEEK");
                switch (weekval) {
                    case 1:
                        week = "星期一";
                        break;
                    case 2:
                        week = "星期二";
                        break;
                    case 3:
                        week = "星期三";
                        break;
                    case 4:
                        week = "星期四";
                        break;
                    case 5:
                        week = "星期五";
                        break;
                    case 6:
                        week = "星期六";
                        break;
                    case 7:
                        week = "星期日";
                        break;
                }

            }
            StringBuffer projectName = new StringBuffer();
            if (!StringUtils.isEmpty((String) record.get("COURSEID"))) {
                courseId = (String) record.get("COURSEID");
            }
            if (!StringUtils.isEmpty((String) record.get("COURSENAME"))) {
                courseName = (String) record.get("COURSENAME");
            }
            if (!StringUtils.isEmpty((String) record.get("STARTIME"))) {
                startTime = (String) record.get("STARTIME");
                date = startTime.substring(0, 4) + "年" + startTime.substring(4, 6) + "月"+startTime.substring(6, 8) + "日";
                startTime = formattingDate(startTime);
            }
            if (!StringUtils.isEmpty((String) record.get("ENDTIME"))) {
                endTime = (String) record.get("ENDTIME");
                endTime = formattingDate(endTime);
            }

//            StringBuffer score = new StringBuffer();
//            获得成绩
//            if (null != (Integer) record.get("scheduleId")) {
//                Integer courseIds = (Integer) record.get("scheduleId");
//                score = score.append("<table>").append(getProjects(courseIds, classSchedule)).append("</table>");
//            }
            //获得签到状态
            if (null != (Integer) record.get("ID") && null != classSchedule.getChildId()) {
                Integer scheduleDetailId = (Integer) record.get("ID");
                status = getCourseStatus(scheduleDetailId, classSchedule.getChildId());
            }


//            vo.setScore(score);
            vo.setDate(date);
            vo.setWeekDay(week);
            vo.setSignStatus(status);
            vo.setProject(projectName);
            vo.setCourseId(courseId);
            vo.setCourseName(courseName);
            vo.setStartTime(startTime);
            vo.setEndTime(endTime);
            vo.setTime(startTime + "-" + endTime);
            list.add(vo);
        }
        pm.setTotalCount(totalCount);
        pm.setPageData(list);

        return pm;

    }


    //    1：签到 2：请假 3：旷课
    public String getCourseStatus(Integer courseId, String childId) {
        StringBuilder sql = new StringBuilder();
        StringBuilder whereSql = new StringBuilder();
        sql.append(SqlBuilder.SQL_SELECT).append("sign_type").append(SqlBuilder.SQL_FROM);
        whereSql.append("t_child_schedule_status")
                .append(SqlBuilder.SQL_WHERE).append("child_id = :childId and schedule_id =:courseId");
        Map<String, Object> paramMap = new HashMap<String, Object>();
        paramMap.put("childId", childId);
        paramMap.put("courseId", courseId);
        sql.append(whereSql);
        logger.info("SQL : " + "\"" + sql.toString() + "\"");

        List<Integer> result = namedTemplate.queryForList(sql.toString(), paramMap, Integer.class);
        String status = "无状态";
        if (0 < result.size()) {
            if (result.get(0) == 1) {
                status = "签到";
            } else if (result.get(0) == 2) {
                status = "请假";
            } else if (result.get(0) == 3) {
                status = "旷课";
            }
        }
        return status;
    }

    private String formattingDate(String strDate) {
        // 准备第一个模板，从字符串中提取出日期数字
        String pat1 = "yyyyMMddHHmmss";
        // 准备第二个模板，将提取后的日期数字变为指定的格式
        String pat2 = "HH:mm:ss";
        SimpleDateFormat sdf1 = new SimpleDateFormat(pat1);        // 实例化模板对象
        SimpleDateFormat sdf2 = new SimpleDateFormat(pat2);        // 实例化模板对象
        Date d = null;
        try {
            d = sdf1.parse(strDate);   // 将给定的字符串中的日期提取出来
        } catch (Exception e) {            // 如果提供的字符串格式有错误，则进行异常处理
            e.printStackTrace();       // 打印异常信息
        }
        String create = sdf2.format(d);
        return create;

    }


    public StringBuffer getProjects(Integer scheduleId, ClassSchedule classSchedule) {
        StringBuilder sql = new StringBuilder();
        StringBuilder whereSql = new StringBuilder();
        sql.append(SqlBuilder.SQL_SELECT).append("si.questions as QUESTIONS,si.name as NAME,s.id AS ID,s.score AS SCORE,s.memo AS MEMO,s.create_time AS CREATETIME,s.project_code AS PROJECTCODE ").append(SqlBuilder.SQL_FROM);
        whereSql.append("score s\n" +
                "LEFT JOIN sensory_integration si on si.code = s.project_code")
                .append(SqlBuilder.SQL_WHERE).append("1=1");
        Map<String, Object> paramMap = new HashMap<String, Object>();
        if (StringUtils.isNotEmpty(classSchedule.getChildId())) {
            whereSql.append(SqlBuilder.SQL_AND).append("s.child_id").append(" = ").append(":child_id");
            paramMap.put("child_id", classSchedule.getChildId());
        }
//        if (StringUtils.isNotEmpty(classSchedule.getTeacherId())) {
//            whereSql.append(SqlBuilder.SQL_AND).append("s.teacher_id").append(" = ").append(":teacher_id");
//            paramMap.put("teacher_id", classSchedule.getTeacherId());
//        }
        if (null != scheduleId) {
            whereSql.append(SqlBuilder.SQL_AND).append("s.schedule_id").append(" = ").append(":schedule_id");
            paramMap.put("schedule_id", scheduleId);
        }
        sql.append(whereSql);
        logger.info("SQL : " + "\"" + sql.toString() + "\"");
        List<Map<String, Object>> resultList = namedTemplate.queryForList(sql.toString(), paramMap);
        Map<String, String> columnMapper = new HashMap<>();
        List<Score> list = new ArrayList<Score>();
        StringBuffer stringBuffer = new StringBuffer();
        for (Iterator<Map<String, Object>> iterator = resultList.iterator(); iterator.hasNext(); ) {
            Map<String, Object> record = iterator.next();
            String name = "没有题目名字";
            String scores = "没有成绩";
            String memo = "没有评价";
            String projectCode = "";
            String question = "";
            Date createTime = null;

            if (!StringUtils.isEmpty((String) record.get("NAME"))) {
                name = (String) record.get("NAME");
            }

            if (!StringUtils.isEmpty((String) record.get("SCORE")) && !StringUtils.isEmpty((String) record.get("QUESTIONS"))) {
                scores = (String) record.get("SCORE");
                question = (String) record.get("QUESTIONS");
                scores = grades(question, scores);
            }

            if (!StringUtils.isEmpty((String) record.get("MEMO"))) {
                memo = (String) record.get("MEMO");
            }
            if (!StringUtils.isEmpty((String) record.get("PROJECTCODE"))) {
                projectCode = (String) record.get("PROJECTCODE");
            }
            if (null != (Date) record.get("CREATETIME")) {
                createTime = (Date) record.get("CREATETIME");
            }

            stringBuffer.append("<tr><td><b>").append(name).append("</b>:&nbsp;&nbsp;").append(scores).append("</td><td><b>备注</b>:&nbsp;").append(memo).append("</td></tr>");
        }

        return stringBuffer;
    }

    //解析成绩和题目
    private String grades(String qus, String sco) {
        String grades = "";
        //获得问题
        JSONArray qusJson = new JSONArray();
        JSONArray scoJson = new JSONArray();
        try {
            qusJson = (JSONArray) JSONArray.parse(qus);
            scoJson = (JSONArray) JSONArray.parse(sco);
        } catch (Exception e) {
            e.printStackTrace();
            return grades;
        }
        if (qusJson == null || scoJson == null) {
            return grades;
        }

        int queSize = scoJson.size();
        int scoSize = scoJson.size();
        int Size = (queSize >= scoSize) ? queSize : scoSize;
        //获得成绩
        for (int i = 0; i < Size; i++) {
            JSONObject qusJsonObj = qusJson.getJSONObject(i);
            //// TODO: 16/12/2  
            JSONObject scoJsonObj = scoJson.getJSONObject(i);
            //题目
            if (qusJsonObj.get("desc") != null) {
                grades += qusJsonObj.get("desc") + ":&nbsp;&nbsp;";
            }
            //成绩
            if (scoJsonObj.get("minute") != null) {
                grades += scoJsonObj.get("minute") + "分";
            }
            if (scoJsonObj.get("second") != null) {
                grades += scoJsonObj.get("second") + "秒&nbsp;&nbsp;";
            }
            if (scoJsonObj.get("value") != null) {
                grades += scoJsonObj.get("value") + "个&nbsp;&nbsp;";
            }
            if (scoJsonObj.get("values") != null) {
                if (qusJsonObj.get("options") != null) {
//                    解析json
                    JSONArray opt = (JSONArray) qusJsonObj.get("options");
                    JSONArray val = (JSONArray) scoJsonObj.get("values");
                    grades += getOptions(val, opt);
                }
            }
        }
        if (grades.equals("")) {
            grades = "暂无成绩";
        }
        return grades;
    }


    private String getOptions(JSONArray val, JSONArray opt) {
        String optionStr = "";
        if (val == null || opt == null) {
            return optionStr;
        }
        //解析val数组 如果["1","2"]; 获得结果数组
        if (val.size() == 0 || opt.size() == 0) {
            return optionStr;
        }
        String indexStr;
        String valStr;
        int size = opt.size();
        for (int i = 0; i < size; i++) {
            JSONObject Jsonobj = opt.getJSONObject(i);
            for (Object index : val) {
                indexStr = index.toString();
                valStr = (String) Jsonobj.get("value").toString();
                if (indexStr.equals(valStr)) {
                    optionStr = (String) Jsonobj.get("desc") + "&nbsp;&nbsp;";
                }
            }
        }
        return optionStr;
    }
}
